# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import os

# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import MySQLdb

class QidianHotPipeline:
    def process_item(self, item, spider):
        return item

class MySQLPipeline:
    def open_spider(self, spider):
        db_name = spider.settings.get("MYSQL_DB_NAME", "qidian")
        host = spider.settings.get("MYSQL_HOST", "localhost")
        user = spider.settings.get("MYSQL_USER", "root")
        db_pwd = spider.settings.get("MYSQL_PASSWORD")

        self.db_conn = MySQLdb.connect(db = db_name,
                                       host = host,
                                       user = user,
                                       password = db_pwd,
                                       charset = "utf8")
        self.db_cursor = self.db_conn.cursor()

    def process_item(self, item, spider):
        values = (item["name"], item["author"], item["type"], item["form"])
        sql = "insert into hot_sales(name, author, type, form) values(%s, %s, %s, %s)"
        self.db_cursor.execute(sql, values)

        return item
    
    def close_spider(self, spider):
        self.db_conn.commit()
        self.db_cursor.close()
        self.db_conn.close()